﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Web.Configuration;
using System.Configuration;

namespace DreamBox.Net.Data
{
    public class DataAccess
    {
        private SqlConnection conn;
        public DataAccess()
        {
            this.conn = new SqlConnection(WebConfigurationManager.AppSettings["ConnectionStrings"].ToString());
        }
        /// <summary>
        /// 基于存储过程操作(用于接收存储过程返回的参数和输出参数)
        /// </summary>
        /// <param name="ProcedureName"></param>
        /// <param name="parameter"></param>
        /// <returns>返回一个哈希数据集</returns>
        public List<Object> ExecProcedureNonQueryList(string ProcedureName, List<SqlParameter> parameters)
        {
            GHashTable hashTbl = new GHashTable();//存储返回值
            SqlCommand cmd = null;
            DataSet ds;
            SqlDataAdapter sda;
            try
            {
                ConnectionOpen();
                cmd = new SqlCommand();
                cmd.CommandText = ProcedureName;
                //指定执行的类型为存储过程
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conn;
                //添加参数
                foreach (SqlParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
                ds = new DataSet();
                sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                //数据填充
                sda.Fill(ds);
                //获取存储过程返回的值
                foreach (SqlParameter sp in cmd.Parameters)
                {
                    if (sp.Direction != ParameterDirection.Input)
                    {
                        hashTbl.Add(sp.ParameterName, sp.Value);
                    }
                }
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
            //返回参数设置
            List<Object> list = new List<Object>();
            list.Add(hashTbl);
            list.Add(ds);
            return list;
        }
        /// <summary>
        /// 基于存储过程操作(返回影响的行数)
        /// </summary>
        /// <param name="ProcedureName"></param>
        /// <param name="parameter"></param>
        /// <returns>返回一个DataSet数据集</returns>
        public GHashTable ExecuteProcedureNonQueryHashTable(string ProcedureName, List<SqlParameter> parameters)
        {
            GHashTable hashTbl = new GHashTable();//存储返回值
            SqlCommand cmd=null;
            try
            {
                ConnectionOpen();
                cmd= new SqlCommand();
                cmd.CommandText = ProcedureName;
                //指定执行的类型为存储过程
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conn;
                //添加参数
                foreach (SqlParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
                cmd.ExecuteNonQuery();
                //获取存储过程返回的值
                foreach (SqlParameter sp in cmd.Parameters)
                {
                    if (sp.Direction != ParameterDirection.Input)
                    {
                        hashTbl.Add(sp.ParameterName, sp.Value);
                    }
                }
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
            return hashTbl;
        }
        /// <summary>
        /// 基于存储过程操作(返回影响的行数)
        /// </summary>
        /// <param name="ProcedureName"></param>
        /// <param name="parameter"></param>
        /// <returns>返回一个DataSet数据集</returns>
        public int ExecuteProcedureNonQuery(string ProcedureName, List<SqlParameter> parameters)
        {
            int result;
            SqlCommand cmd = null;
            try
            {
                ConnectionOpen();
                cmd = new SqlCommand();
                cmd.CommandText = ProcedureName;
                //指定执行的类型为存储过程
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conn;
                //添加参数
                foreach (SqlParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
                result = cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
            return result;
        }
        /// <summary>
        /// 基于存储过程操作
        /// </summary>
        /// <param name="ProcedureName"></param>
        /// <param name="parameter"></param>
        /// <returns>返回一个DataSet数据集</returns>
        public DataSet ExecuteProcedure(string ProcedureName,List<SqlParameter> parameters)
        {
            DataSet ds;
            SqlDataAdapter sda;
            SqlCommand cmd = null;
            try
            {
                ConnectionOpen();
                cmd = new SqlCommand();
                cmd.CommandText = ProcedureName;
                //指定执行的类型为存储过程
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conn;
                //添加参数
                //添加参数
                foreach (SqlParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
                ds = new DataSet();
                sda = new SqlDataAdapter();
                sda.SelectCommand = cmd;
                //数据填充
                sda.Fill(ds);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
            return ds;
        }
        /// <summary>
        /// 基于存储过程的数据读取
        /// </summary>
        /// <param name="ProcedureName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DbTable ExecuteProcedureForReader(string ProcedureName, List<SqlParameter> parameters)
        {
            SqlDataReader sdr=null;
            SqlCommand cmd = null;
            DbTable dt = null;
            try
            {
                ConnectionOpen();
                cmd = new SqlCommand();
                cmd.CommandText = ProcedureName;
                //指定执行的类型为存储过程
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conn;
                //添加参数
                //添加参数
                foreach (SqlParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
                sdr = cmd.ExecuteReader();
                dt = new DbTable();
                while (sdr.Read())
                {
                    DbRow dr = dt.NewRow();
                    //读列
                    for (int i = 0; i < sdr.FieldCount; i++)
                    {
                        DbColumn dc = new DbColumn();
                        dc.Name = sdr.GetName(i);
                        dc.Value = sdr.GetValue(i);
                        dc.DataType = sdr.GetFieldType(i);
                        dr.Add(dc);
                    }
                    dt.Add(dr);
                }
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                sdr.Close();
            }
            return dt;
        }
        /// <summary>
        /// 基于SQL语句的查询操作
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns>返回一个DataSet数据集</returns>
        public DataSet Select(string sqlstr, List<SqlParameter> parameters)
        {
            SqlDataAdapter sda;
            DataSet ds = new DataSet();
            SqlCommand cmd = null;
            try
            {
                ConnectionOpen();
                sda = new SqlDataAdapter();
                cmd = new SqlCommand();
                cmd.CommandText = sqlstr;
                cmd.Connection = conn;
                //添加参数
                foreach (SqlParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
                sda.SelectCommand = cmd;
                //数据填充
                sda.Fill(ds);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
            return ds;
        }
        /// <summary>
        /// 数据读取
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public DbTable ExecuteSQL(string sqlstr, List<SqlParameter> parameters)
        {
            SqlDataReader sdr =null;
            SqlCommand cmd = null;
            DbTable dt = null;
            try
            {
                ConnectionOpen();
                cmd = new SqlCommand();
                cmd.CommandText = sqlstr;
                cmd.Connection = conn;
                
                //添加参数
                //添加参数
                foreach (SqlParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
                sdr = cmd.ExecuteReader();
                dt = new DbTable();
                while (sdr.Read())
                {
                    DbRow dr = dt.NewRow();
                    //读列
                    for (int i = 0; i < sdr.FieldCount; i++)
                    {
                        DbColumn dc = new DbColumn();
                        dc.Name = sdr.GetName(i);
                        dc.Value = sdr.GetValue(i);
                        dc.DataType = sdr.GetFieldType(i);
                        dr.Add(dc);
                    }
                    dt.Add(dr);
                }
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                sdr.Close();
            }
            return dt;
        }
        /// <summary>
        /// 基于SQL语句的查询操作
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns>返回一个DataSet数据集</returns>
        public DataSet ExecuteSQL(string sqlstr, params SqlParameter[] parameter)
        {
            SqlDataAdapter sda;
            DataSet ds = new DataSet();
            SqlCommand cmd = null;
            try
            {
                ConnectionOpen();
                sda = new SqlDataAdapter();
                cmd = new SqlCommand();
                cmd.CommandText = sqlstr;
                cmd.Connection = conn;
                //添加参数
                cmd.Parameters.AddRange(parameter);
                sda.SelectCommand = cmd;
                //数据填充
                sda.Fill(ds);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
            return ds;
        }
        /// <summary>
        /// 基于SQL语句的增删改操作
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns></returns>
        public bool ExecuteSqlNonQuery(string sqlstr,params SqlParameter[] parameter)
        {
            int count;
            SqlCommand cmd = null;
            try
            {
                ConnectionOpen();
                cmd = new SqlCommand();
                cmd.CommandText = sqlstr;
                cmd.Connection = conn;
                //添加参数
                cmd.Parameters.AddRange(parameter);
                //获取受影响的行数
                count= cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
            return count > 0 ? true : false;
        }
        /// <summary>
        /// 基于SQL语句的增删改操作
        /// </summary>
        /// <param name="sqlstr">SQL语句</param>
        /// <param name="parameters">List<SqlParameter>类型</param>
        /// <returns></returns>
        public bool ExecuteSqlNonQuery(string sqlstr, List<SqlParameter> parameters)
        {
            int count;
            SqlCommand cmd=null;
            try
            {
                ConnectionOpen();
                cmd = new SqlCommand();
                cmd.CommandText = sqlstr;
                cmd.Connection = conn;
                //添加参数
                foreach (SqlParameter p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
                //获取受影响的行数
                count = cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }
            return count > 0 ? true : false;
        }
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        private void ConnectionOpen()
        {
            if (conn.State== ConnectionState.Closed)
            {
                try
                {
                    conn.Open();
                }
                catch { }
            }
        }
    }
}
